﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Xml.XPath;
using NUnit.Framework;
using Rhino.Mocks;
using Rhino.Mocks.Constraints;
using System.Linq;
using System.Linq.Expressions;

namespace IPAMS.Data
{
	public class ExcelLoader : IDataLoader
	{
		private readonly IMapper mapper;
		private readonly string sourceFilePath;

		public ExcelLoader(string sourceFilePath, IMapper mapper)
		{
			this.sourceFilePath = sourceFilePath;
			this.mapper = mapper;
		}

		#region IDataLoader Members

		public IPAMSDataSet Load()
		{
			DataSet dataSet = GetWorkbookAsDataSet();
			foreach (DataTable table in dataSet.Tables)
			{
				foreach (DataColumn column in table.Columns)
					column.ColumnName = mapper.MapColumnName(column);
				table.TableName = mapper.MapTableName(table);
			}
						
			IPAMSDataSet ipamsDataSet = new IPAMSDataSet();
//			ipamsDataSet.EnforceConstraints = false;
			ipamsDataSet.Merge(dataSet, true, MissingSchemaAction.Ignore);
			return ipamsDataSet;
		}

		#endregion

		private DataSet GetWorkbookAsDataSet()
		{
			OleDbConnection excelConnection =
				new OleDbConnection(
					string.Format(
						@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;",
						sourceFilePath));
			OleDbCommand excelCommand = new OleDbCommand();
			excelCommand.Connection = excelConnection;
			OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(excelCommand);

			excelConnection.Open();
			DataTable excelSheets =
				excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
				                                    new object[] {null, null, null, "TABLE"});
			DataSet excelDataSet = new DataSet();

			foreach (DataRow tabNameRow in excelSheets.Rows)
			{
				string tabName = (string) tabNameRow["TABLE_NAME"];
				if( tabName.EndsWith("_"))
					continue;
				DataTable tabTable =
					excelDataSet.Tables.Add(tabName.Remove(tabName.Length - 1, 1));
				excelCommand.CommandText = string.Format(@"SELECT * FROM [{0}]", tabName);
				ExcelAdapter.Fill(tabTable);
			}

			excelConnection.Close();
			return excelDataSet;
		}
	}

	[TestFixture]
	public class ExcelLoaderTest
	{
		private delegate string MapColumnNameDelegate(DataColumn column);

		static string DefaultColumnName(DataColumn column)
		{
			return column.ColumnName;
		}

		private delegate string MapTableNameDelegate(DataTable table);

		static string DefaultTableName(DataTable table)
		{
			return table.TableName;
		}

		[Test]
		public void LoadsOneMappedTableWithOneLine()
		{
			string currencyOnlyFileName = @"OneCurrency.xls";
			MockRepository repository = new MockRepository();
			IMapper mapperMock = repository.DynamicMock<IMapper>();
			using (repository.Unordered())
			{
				Expect.Call(mapperMock.MapColumnName(null)).Constraints(
					Property.Value("ColumnName", "CurrencyCode")).Return("Code").Repeat.Any();
				Expect.Call(mapperMock.MapColumnName(null)).Constraints(
					Property.Value("ColumnName", "CurrencyName")).Return("Name").Repeat.Any();
				Expect.Call(mapperMock.MapColumnName(null)).IgnoreArguments().Do(new MapColumnNameDelegate(DefaultColumnName)).Repeat.Any();
				Expect.Call(mapperMock.MapTableName(null)).Constraints(
					Property.Value("TableName", "Currencies")).Return("Currency").Repeat.Any();
				Expect.Call(mapperMock.MapTableName(null)).IgnoreArguments().Do(new MapTableNameDelegate(DefaultTableName)).Repeat.Any();
			}
			repository.ReplayAll();
			ExcelLoader loader = CreateLoader(currencyOnlyFileName, mapperMock);
			IPAMSDataSet actualDataSet = loader.Load();
			Assert.AreEqual(1, actualDataSet.Currency.Rows.Count);
		}

		[Test]
		public void LoadsOneMappedTableWithTwoLines()
		{
			string currencyOnlyFileName = @"TwoCurrencies.xls";
			MockRepository repository = new MockRepository();
			IMapper mapperMock = repository.DynamicMock<IMapper>();
			using (repository.Unordered())
			{
				Expect.Call(mapperMock.MapColumnName(null)).Constraints(
					Property.Value("ColumnName", "CurrencyCode")).Return("Code").Repeat.Any();
				Expect.Call(mapperMock.MapColumnName(null)).Constraints(
					Property.Value("ColumnName", "CurrencyName")).Return("Name").Repeat.Any();
				Expect.Call(mapperMock.MapColumnName(null)).IgnoreArguments().Do(new MapColumnNameDelegate(DefaultColumnName)).Repeat.Any();
				Expect.Call(mapperMock.MapTableName(null)).Constraints(
					Property.Value("TableName", "Currencies")).Return("Currency").Repeat.Any();
				Expect.Call(mapperMock.MapTableName(null)).IgnoreArguments().Do(new MapTableNameDelegate(DefaultTableName)).Repeat.Any();
			}
			repository.ReplayAll();
			ExcelLoader loader = CreateLoader(currencyOnlyFileName, mapperMock);
			IPAMSDataSet actualDataSet = loader.Load();
			Assert.AreEqual(2, actualDataSet.Currency.Rows.Count);
		}

		[Test]
		public void TTT()
		{
			DataMapper mapper = new DataMapper(new XPathDocument(@"Data\ExcelToDSMapping.xml"));
			ExcelLoader loader = new ExcelLoader(@"D:\Documents\Technion\Maman Project\FinalSchema - Copy (2).xls", mapper);
			IPAMSDataSet load = loader.Load();
		}

		private static ExcelLoader CreateLoader(string currencyOnlyFileName, IMapper mapper)
		{
			return new ExcelLoader(@"D:\Documents\Visual Studio 2008\Projects\Technion\IPAMS\IPAMS version 1.0.0.0 documented\IPAMS\Tests\" + currencyOnlyFileName, mapper);
		}
	}
}